refresh table jms_dwd.dwd_yl_oms_oms_order_hf;
refresh table jms_dwd.dwd_tab_reback_transfer_express_base_hi;
refresh table jms_dim.dim_yl_oms_order_interceptor_hi;
with order_hf as (--订单表
    select waybill_id             --运单号
         , input_time             --订单录入时间
         , third_code             --三段码
         , dispatch_network_code  --派件网点code
         , receiver_province_name --收件省份
         , receiver_city_name     --收件城市
         , receiver_area_name     --收件区域
    from jms_dwd.dwd_yl_oms_oms_order_hf a
    where dt <= '{{ execution_date | cst_hour }}'
      and substr(dt, 1, 10) >= date_add(substr('{{ execution_date | cst_hour }}', 1, 10), -4)
      and concat(substr(a.input_time, 1, 10), '-', substr(a.input_time, 12, 2)) <= '{{ execution_date | cst_hour }}'
      and substr(a.input_time, 1, 10) >= date_add(substr('{{ execution_date | cst_hour }}', 1, 10), -4)
      and order_source_code not in ('D09', 'D607', 'D608', 'D60', 'D69')
      and waybill_id is not null
      and order_status_code <> '104'
      and waybill_rnk = 1
),
     transfer_express as (--退转件
         select waybill_no
         from jms_dwd.dwd_tab_reback_transfer_express_base_hi
         where dt <= '{{ execution_date | cst_hour }}'
           and substr(dt, 1, 10) >= date_add(substr('{{ execution_date | cst_hour }}', 1, 10), -4)
           and waybill_no is not null
         group by waybill_no
     ),
     interceptor_network as (--拦截表 --网点
         select network_code
         from (
                  select network_code
                  from jms_dim.dim_yl_oms_order_interceptor_hi
                  where data_type = 'network'
                    and is_delete = 0 --未删除
                    and is_enable = 1 --已启用
                    and effect_time_type = 0
                  union all
                  select network_code
                  from jms_dim.dim_yl_oms_order_interceptor_hi
                  where is_delete = 0
                    and is_enable = 1
                    and data_type = 'network'
                    and effect_time_type = 1
                    and concat(substr('{{ execution_date | cst_hour }}', 1, 10), ' ',
                               substr('{{ execution_date | cst_hour }}', 12, 2), ':00:00') >= effect_time_start
                    and concat(substr('{{ execution_date | cst_hour }}', 1, 10), ' ',
                               substr('{{ execution_date | cst_hour }}', 12, 2), ':00:00') <= effect_time_end
              ) a
         group by network_code
     ),
     interceptor_third as (--拦截表 --三段码+网点
         select the_third_code
              , network_code
         from (
                  select the_third_code
                       , network_code
                  from jms_dim.dim_yl_oms_order_interceptor_hi
                  where is_delete = 0
                    and is_enable = 1
                    and data_type = 'the_third_segment_code'
                    and effect_time_type = 0
                  union all
                  select the_third_code
                       , network_code
                  from jms_dim.dim_yl_oms_order_interceptor_hi
                  where is_delete = 0
                    and is_enable = 1
                    and data_type = 'the_third_segment_code'
                    and effect_time_type = 1
                    and concat(substr('{{ execution_date | cst_hour }}', 1, 10), ' ',
                               substr('{{ execution_date | cst_hour }}', 12, 2), ':00:00') >= effect_time_start
                    and concat(substr('{{ execution_date | cst_hour }}', 1, 10), ' ',
                               substr('{{ execution_date | cst_hour }}', 12, 2), ':00:00') <= effect_time_end
              ) a
         group by the_third_code
                , network_code
     ),
     interceptor_area_province as (--拦截表 --区域
         select province
              , city
              , area
         from (
                  select province
                       , null as city
                       , null as area
                  from jms_dim.dim_yl_oms_order_interceptor_hi
                  where is_delete = 0
                    and is_enable = 1
                    and data_type = 'area'
                    and effect_time_type = 0
                    and province is not null
                    and city is null
                  union all
                  select province
                       , null as city
                       , null as area
                  from jms_dim.dim_yl_oms_order_interceptor_hi
                  where is_delete = 0
                    and is_enable = 1
                    and data_type = 'area'
                    and effect_time_type = 1
                    and concat(substr('{{ execution_date | cst_hour }}', 1, 10), ' ',
                               substr('{{ execution_date | cst_hour }}', 12, 2), ':00:00') >= effect_time_start
                    and concat(substr('{{ execution_date | cst_hour }}', 1, 10), ' ',
                               substr('{{ execution_date | cst_hour }}', 12, 2), ':00:00') <= effect_time_end
                    and province is not null
                    and city is null
              ) a
         group by province
                , city
                , area
     ),

     interceptor_area_city as (--拦截表 --区域
         select province
              , city
              , area
         from (
                  select province
                       , city
                       , null as area
                  from jms_dim.dim_yl_oms_order_interceptor_hi
                  where is_delete = 0
                    and is_enable = 1
                    and data_type = 'area'
                    and effect_time_type = 0
                    and province is not null
                    and city is not null
                    and area is null
                  union all
                  select province
                       , city
                       , null as area
                  from jms_dim.dim_yl_oms_order_interceptor_hi
                  where is_delete = 0
                    and is_enable = 1
                    and data_type = 'area'
                    and effect_time_type = 1
                    and concat(substr('{{ execution_date | cst_hour }}', 1, 10), ' ',
                               substr('{{ execution_date | cst_hour }}', 12, 2), ':00:00') >= effect_time_start
                    and concat(substr('{{ execution_date | cst_hour }}', 1, 10), ' ',
                               substr('{{ execution_date | cst_hour }}', 12, 2), ':00:00') <= effect_time_end
                    and province is not null
                    and city is not null
                    and area is null
              ) a
         group by province
                , city
                , area
     ),
     interceptor_area_area as (--拦截表 --区域
         select province
              , city
              , area
         from (
                  select province
                       , city
                       , area
                  from jms_dim.dim_yl_oms_order_interceptor_hi
                  where is_delete = 0
                    and is_enable = 1
                    and data_type = 'area'
                    and effect_time_type = 0
                    and province is not null
                    and city is not null
                    and area is not null
                  union all
                  select province
                       , city
                       , area
                  from jms_dim.dim_yl_oms_order_interceptor_hi
                  where is_delete = 0
                    and is_enable = 1
                    and data_type = 'area'
                    and effect_time_type = 1
                    and concat(substr('{{ execution_date | cst_hour }}', 1, 10), ' ',
                               substr('{{ execution_date | cst_hour }}', 12, 2), ':00:00') >= effect_time_start
                    and concat(substr('{{ execution_date | cst_hour }}', 1, 10), ' ',
                               substr('{{ execution_date | cst_hour }}', 12, 2), ':00:00') <= effect_time_end
                    and province is not null
                    and city is not null
                    and area is not null
              ) a
         group by province
                , city
                , area
     ),
     master_data_base1 as (--1.剔除退转数据 2.派件异常,三段码+派件异常进行打标
         select t1.waybill_id                                                          --运单
              , t1.input_time                                                          --订单录入时间
              , t1.third_code                                                          --三段码
              , t1.receiver_province_name                                              --收件省份id
              , t1.receiver_city_name                                                  --收件城市id
              , t1.receiver_area_name                                                  --收件区域id
              , t1.dispatch_network_code                                               --派件网点
              , t4.network_code                         as inter_dispatch_network_code --拦截派件网点
              , t5.the_third_code                       as inter_third_code            --拦截三段码
              , t5.network_code                         as inter_third_network_code    --拦截三段码网点
              , if(t4.network_code is not null, 1, 0)   as re_dispatch_flag            --派件网点和拦截一直=1
              , if(t5.the_third_code is not null, 1, 0) as re_third_flag               --派件网点和三段码与拦截一直=1
         from order_hf t1
                  left anti
                  join transfer_express t2 --剔除退转表数据
                       on t1.waybill_id = t2.waybill_no
                  left join interceptor_network t4 --派件网点异常
                            on t1.dispatch_network_code = t4.network_code
                  left join interceptor_third t5 --派件网点+三段码异常
                            on t1.dispatch_network_code = t5.network_code
                                and t1.third_code = t5.the_third_code
         --                   left join interceptor_network t3 --寄件网点异常
--                             on t1.pick_network_code = t3.network_code
     ),
     master_data_base2 as (--对区域异常进行打标
         select t1.waybill_id                                         --运单
              , t1.input_time                                         --订单录入时间
              , t1.third_code                                         --三段码
              , t1.receiver_province_name                             --收件省份id
              , t1.receiver_city_name                                 --收件城市id
              , t1.receiver_area_name                                 --收件区域id
              , t1.dispatch_network_code                              --派件网点
              , t1.inter_dispatch_network_code                        --拦截派件网点
              , t1.inter_third_code                                   --拦截三段码
              , t1.inter_third_network_code                           --拦截三段码网点
              , t1.re_dispatch_flag                                   --派件网点和拦截一直=1
              , t1.re_third_flag                                      --派件网点和三段码与拦截一直=1
              , if(t2.province is not null, 1, 0) as re_province_flag --派件省和拦截一直=1
              , if(t3.city is not null, 1, 0)     as re_city_flag     --派件市和拦截一直=1
              , if(t4.area is not null, 1, 0)     as re_area_flag     --派件区域和拦截一直=1
              , t2.province                       as inter_province   --拦截省
              , t3.city                           as inter_city       --拦截市
              , t4.area                           as inter_area       --拦截区域
         from master_data_base1 t1
                  left join interceptor_area_province t2
                            on t1.receiver_province_name = t2.province
                  left join interceptor_area_city t3
                            on t1.receiver_city_name = t3.city
                                and t1.receiver_province_name = t3.province
                  left join interceptor_area_area t4
                            on t1.receiver_area_name = t4.area
                                and t1.receiver_province_name = t4.province
                                and t1.receiver_city_name = t2.city
     )
insert
overwrite
table
jms_dm.dm_tab_interceptor_waybill_kafka_middle_hi
partition
(
dt
)
select waybill_id                                             as waybill_no --运单号
     , input_time                                             as input_time --订单录入时间
     , substr(input_time, 1, 10)                              as date_time  --订单录入日期
     , if(re_dispatch_flag = 1 or re_third_flag = 1 or re_province_flag = 1 or
          re_city_flag = 1 or re_area_flag = 1, 1, 0)         as status     --运单转态
     , from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') as sys_time   --系统运行时间
     , dispatch_network_code
     , third_code
     , receiver_province_name
     , receiver_city_name
     , receiver_area_name
     , '{{ execution_date | cst_hour }}'                      as dt
from master_data_base2 t1
distribute by dt,pmod(hash(waybill_no),10)
;
